PostgreSQL WAL
Journaux liées à cette note :
Journal du mercredi 12 février 2025 à 10:44
En travaillant sur le projet Projet 23 - "Ajouter le support pg_basebackup incremental à restic-pg_dump-docker", j'ai découvert que PostgreSQL propose deux protocoles de communication :
- Le premier est le plus connu "Frontend/Backend Protocol". C'est celui qui est utilisé par la commande
psqlou les librairies telles que Postgres.js (en NodeJS), Psycopg (en Python)… - Le second nommé "Streaming Replication Protocol"
Sans vraiment comprendre, en 2020, j'avais activé le protocole streaming replication dans ce POC postgresql-streaming-replication-playground. Mais je n'avais jamais pris conscience de l'existence de ce second protocole.
Les développeurs de PostgreSQL semblent avoir décidé de créer un second protocole parce qu'ils n'ont pas du tout le même objectif.
Streaming Replication Protocol est optimisé dans la transmission des WAL et des snapshots (copie de l'intégralité du dossier PGDATA).
Le protocole Streaming Replication Protocol est entre autres utilisé par pg_basebackup, barman, pgBackRest, ou Patroni.
Comment activer Streaming Replication Protocol ?
Les images Docker Postgres setup par défaut le fichier pg_hba.conf suivant :
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all all scram-sha-256
J'ai compris que par défaut, toutes ces lignes configurent l'accès au Frontend/Backend Protocol.
Seules les lignes qui contiennent replication configurent l'accès au Streaming Replication Protocol.
Pour permettre l'accès au Streaming Replication Protocol par réseau TCP/IP en dehors du container Docker il est nécessaire d'ajouter la ligne suivante :
host replication all all scram-sha-256
Suite à cette découverte, j'ai repensé à :
Pour faire face à ce problème, j'ai exploré fin 2023 une solution basée sur pgBackRest : Implémenter un POC de pgBackRest.
Je suis plus ou moins arrivé au bout de ce POC mais je n'ai pas été satisfait du résultat.
Je n'ai pas réussi à configurer pgBackRest en "pure Docker sidecar".
De plus, j'ai trouvé la restauration du backup difficile à exécuter.
et je me suis demandé si mon échec de configuration de pgBackRest en Docker sidecar n'était pas seulement dû au fait que je n'avais pas activé Streaming Replication Protocol 🤔.
La réponse semble être "oui" et "non".
Je suis tombé sur l'issue suivante : pgbackrest with postgresql in docker.
The problem might be harder than you think unfortunately. If the pgBackRest process is running on the VM (docker host), it will try to connect to PG locally using the unix socket, not the tcp "localhost" connection.
Je pense comprendre que pgBackRest ne permet pas d'utiliser des INET sockets pour communiquer avec PostgreSQL.
Toutefois, je me dis que je pourrais partager le volume PGDATA avec le sidecar pgBackRest pour lui donner accès à l'Unix Socket du Streaming Replication Protocol 🤔.
Entre mon exploration de pg_basebackup, mes envies de tester barman et de continuer mon POC pgBackRest… je me dis que je ne suis pas encore au bout de ce Yak!.
Journal du dimanche 09 février 2025 à 17:05
J'utilise depuis 2019 les containers Docker suivant en sidecar pour sauvegarder automatiquement et régulièrement directement un volume Docker et un volume PostgreSQL :
restic-pg_dump-docker est très pratique et facile d'usage, voici un exemple d'utilisation dans un docker-compose.yml :
restic-pg-dump:
image: stephaneklein/restic-pg_dump:latest
environment:
AWS_ACCESS_KEY_ID: "admin"
AWS_SECRET_ACCESS_KEY: "password"
RESTIC_REPOSITORY: "s3:http://minio:9000/bucket1"
RESTIC_PASSWORD: secret
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
POSTGRES_HOST: postgres
POSTGRES_DB: postgres
postgres:
image: postgres:16.1
environment:
POSTGRES_USER: postgres
POSTGRES_DB: postgres
POSTGRES_PASSWORD: password
ports:
- "5432:5432"
volumes:
- ./volumes/postgres/:/var/lib/postgresql/data/
healthcheck:
test: ["CMD", "sh", "-c", "pg_isready -U $$POSTGRES_USER -h $$(hostname -i)"]
interval: 10s
start_period: 30s
Il suffit de configurer les paramètres d'accès à l'instance PostgreSQL à sauvegarder et ceux de l'Object Storage où uploader les backups. Rien de plus, 😉.
Pour plus de paramètres, voir la section Configuration du README.md.
Cependant, je ne suis pas totalement satisfait de restic-pg_dump-docker. Cet outil effectue seulement des sauvegardes complètes de la base de données.
Ceci ne pose généralement pas trop de problème quand la base de données est d'une taille modeste, mais c'est bien plus compliqué dès que celle-ci fait, par exemple, plusieurs centaines de mégas.
Pour faire face à ce problème, j'ai exploré fin 2023 une solution basée sur pgBackRest : Implémenter un POC de pgBackRest.
Je suis plus ou moins arrivé au bout de ce POC mais je n'ai pas été satisfait du résultat.
Je n'ai pas réussi à configurer pgBackRest en "pure Docker sidecar".
De plus, j'ai trouvé la restauration du backup difficile à exécuter.
Un élément a changé depuis septembre 2024. Comme je le disais dans cette note 2024-11-03_1151, la version 17 de PostgreSQL propose de nouvelles options de sauvegarde :
- l'outil pg_basebackup qui permet de réaliser les sauvegardes incrémentales,
- et un nouvel utilitaire, pg_combinebackup, qui permet de reconstituer une sauvegarde complète à partir de sauvegardes incrémentales.
Cette nouvelle méthode semble apporter certains avantages par rapport aux solutions basées sur WAL comme pgBackRest ou barman.
Une consommation d'espace réduite :
In this mailing list thread on the Postgres-hackers mailing list, Jakub from EDB ran a test. This is a pgbench test. The idea is that the data size doesn't really change much throughout this test. This is a 24 hour long test. At the start the database is 3.3GB. At the end, the database is 4.3GB. Then, as it's running, it's continuously running pgbench workloads. In those 24 hours, if you looked at the WAL archive, there were 77 GB of WAL produced.
That's a lot of WAL to replay if you wanted to restore to a particular point in time within that timeframe!
Jakub ran one full backup in the beginning and then incremental backups every two hours. The full backup in the beginning is 3.4 GB, but then all the 11 other backups are 3.5 in total, they're essentially one 10th of a full backup size.
Une vitesse de restauration grandement accélérée :
A 10x time safe
What Jakub tested then was the restore to a particular point in time. Previously, to restore to a particular point in time would take more than an hour to replay the WAL versus in this case because we have more frequent, incremental backups, it's going to be much, much faster to restore. In this particular test case 78 minutes compared to 4 minutes. This is a more than a 10 times improvement in recovery time. Of course you won't necessarily always see this amount of benefit, but I think this shows why you might want to do this. It is because you want to enable more frequent backups and incremental backups are the way to do that.
Nombre 2024 j'ai passé un peu de temps à étudier les solutions de backup qui utilisent la nouvelle fonctionnalité de PostgreSQL 17, mais je n'avais rien trouvé
Je viens à nouveau de chercher dans les archives de Postgre Weely, sur GitHub, sur le forum de Restic, etc., et je n'ai rien trouvé d'intéressant.
#JaiDécidé de prendre les choses en main et de faire évoluer le projet restic-pg_dump-docker pour y ajouter le support du backup incrémental de PostgreSQL 17.
Voir : Projet 23 - "Ajouter le support pg_basebackup incremental à restic-pg_dump-docker".